# coding:utf-8
from psycopg2.extras import RealDictCursor
from pymysql.cursors import DictCursor


class Sql(object):
    """
    Attributes:
        db_type:数据库类型： mysql  postgresql
        conn：数据库连接对象
        cursor：数据库游标
    :param db_info 字典
        {
            "db_type": "postgresql",
            "config": {
                "host": "",
                "port": 5432,
                "database": "",
                "user": "",
                "password": ""
            }
        }
        或者：
        {
            "db_type": "mysql",
            "config": {
                "host": "",
                "port": 3306,
                "database": "",
                "user": "",
                "password": "",
                "charset": "utf8"
            }
        }
    """

    def __init__(self, db_info):
        self.db_type = db_info["db_type"]
        db = {
            "mysql": {
                "cursor": {"cursor": DictCursor},
                "plugn": "pymysql"
            },
            "postgresql": {
                "cursor": {"cursor_factory": RealDictCursor},
                "plugn": "psycopg2"
            },
        }
        try:
            conn = f'{db[self.db_type]["plugn"]}.connect(**db_info["config"])'
            self.conn = eval(conn)
            self.cursor = self.conn.cursor(**db[self.db_type]["cursor"])
        except Exception:
            raise ValueError("no support")

    def __del__(self):
        if self.cursor != '':
            self.cursor.close()
        if self.conn != '':
            self.conn.close()

    def try_except(self):
        def wrapper(*args, **kwargs):
            try:
                self(*args, **kwargs)
            except Exception as e:
                print("error: %s" % e)

        return wrapper

    def check_type(self, data):
        if isinstance(data, int):
            is_check = "int"
        elif isinstance(data, str):
            is_check = "str"
        elif isinstance(data, float):
            is_check = "float"
        elif isinstance(data, list):
            is_check = "list"
        elif isinstance(data, tuple):
            is_check = "tuple"
        elif isinstance(data, dict):
            is_check = "dict"
        elif isinstance(data, set):
            is_check = "set"
        else:
            is_check = "unknown"
        return is_check

    def exec(self, sql):
        self.cursor.execute(sql)
        self.conn.commit()
        return True

    def dispose_data(self, data):
        """
        处理curd数据
        :param data: 需要转义的字符串
        :return: 转义后的字符串
        """
        return data
        # is_str = self.check_type(data)
        # if is_str == "str":
        #     data = escape_string(data)
        # return data

    @try_except
    def insert(self, table, data):
        """
        新增数据 多个数据用列表，单个数据用字典
        :param table:
        :param data:
        :return:
        """
        if len(data) <= 0:
            return False
        if self.check_type(data) != 'list':
            data = [data]
        field_list = data[0].keys()
        fields = ",".join(field_list)
        is_true = True
        strs = ','.join(['%s'] * len(field_list))
        preSql = f"insert into {table} ({fields}) values ({strs})"
        sqllist = []
        i = 1
        strdata = ""
        for val in data:
            datalist = []
            for key in field_list:
                datalist.append(self.dispose_data(val[key]))
            sqllist.append(tuple(datalist))
            i += 1
        if strdata != '':
            sqllist.append(strdata)
        try:
            self.cursor.executemany(preSql, sqllist)
            self.conn.commit()
        except Exception:
            is_true = False
            self.conn.rollback()
        return is_true

    def find(self, sql):
        """
        查询单条记录
        :param sql:
        :return: 返回字典
        """
        self.cursor.execute(sql)
        data = self.cursor.fetchone()
        return data

    def find_many(self, sql):
        """
        查询多条数据
        :param sql:
        :return: 返回字典的列表
        """
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        return data

    @try_except
    def update(self, table, fields, where):
        """
        修改单个数据
        :param table: 修改表
        :param fields: 要修改数据字典
        :param where: 条件 字符窜
        :return:
        """
        setstr = ''
        for k, v in fields.items():
            n_v = self.dispose_data(v)
            if setstr == '':
                setstr = "%s='%s'" % (k, n_v)
            else:
                setstr += ",%s='%s'" % (k, n_v)
        sql = "update %s set %s where %s" % (table, setstr, where)
        self.exec(sql)
        return True

    def get_field(self, table, field, wherestr):
        """
        获取某个字段的值
        :param table: 表名
        :param field: 字段
        :param wherestr: 条件 字符串
        :return: 如果没有查询到 返回空字符串
        """
        sql = "select %s from %s where %s" % (field, table, wherestr)
        info = self.find(sql)
        if info:
            return info[field]
        return ""
